<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class Institute extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->tableName = "institute";
		$this->prefix = "ins";
	}

	function getInstitute($where = NULL, $start = NULL, $limit = NULL, $fields = NULL, $query = NULL, $order = "insName", $dir = "ASC", $getAddresses = FALSE) {
		$fieldsQuery = Common::fieldsquery($fields, $query);
		$order = Common::coalesce(array($order, "insName"));
		$dir = Common::coalesce(array($dir, "ASC"));
		$qs =	"
					select
						ins_id, ins_ins_id, ins_name, ins_url_name, ins_city_id, ins_latitude, ins_longitude, ins_priority, ins_presenter, city_name, city_url_name, cou_name, cou_url_name, con_name, con_url_name, t.e2i_image_name, t.e2i_alt, t2.t2_ins_name as parent_ins_name, t3.t3_ins_name as child_ins_name, t4.e2c_temp_flag,
						ifnull(t5_maj_cnt,0) as maj_cnt, ifnull(t5_maj_active_cnt,0) as maj_active_cnt,
						ifnull(t3.t3_ins_cnt, 0) as ins_cnt, ifnull(t3.t3_ins_active_cnt, 0) as ins_active_cnt
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0

						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin() and !defined("OVERRIDE"))?
						(" join ".$this->db->dbprefix."usr2ent on u2e_del = 0 and u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_ins_id = ins_id) and u2e_page_cat ".((defined("ENTITY") and ENTITY)?(" = '".ENTITY."'"):((defined("ENTITY") and (ENTITY) == 0)?("is null"):(" = 'IMP'")))):(""))."

						left join ".$this->db->dbprefix."major on ins_id = maj_ins_id and maj_del = 0
						left join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
						left join ".$this->db->dbprefix."ent2cat on e2c_entity = if(ins_ins_id is null, 'INS', 'FAC') and ins_id = e2c_ent_id and e2c_del = 0 and coalesce(e2c_int, e2c_text) is not null
						left join (
							select
								e2i_ins_id, e2i_image_name, e2i_alt
							from
								".$this->db->dbprefix."ent2image
							where
								e2i_ins_id is not null and e2i_del = 0
							order by
								rand()
						) as t on ins_id = t.e2i_ins_id
						left join (
							select
								ins_id as t2_ins_id, ins_name as t2_ins_name
							from
								".$this->db->dbprefix.$this->tableName."
							where
								ins_del = 0
							order by
								ins_name
						) as t2 on ins_ins_id = t2.t2_ins_id
						left join (
							select
								ins_ins_id as t3_ins_id, group_concat(ins_name order by ins_name separator '<br/>') as t3_ins_name, count(ins_id) as t3_ins_cnt, sum(if(ins_presenter = 'I', 1, 0)) as t3_ins_active_cnt
							from
								".$this->db->dbprefix.$this->tableName."
							where
								ins_del = 0
							group by
								ins_ins_id
							order by
								ins_name
						) as t3 on ins_id = t3.t3_ins_id
						left join (
							select
								e2c_ent_id, e2c_temp_flag
							from
								".$this->db->dbprefix."ent2cat 
							where
								e2c_entity in ('INS', 'FAC') and e2c_del = 0 and e2c_temp_flag = 'I'
							group by
								e2c_ent_id
						) as t4 on t4.e2c_ent_id = ins_id
						left join (
							select
								maj_ins_id as t5_ins_id, count(*) as t5_maj_cnt, sum(if(maj_presenter = 'I', 1, 0)) as t5_maj_active_cnt
							from
								".$this->db->dbprefix."major
								left join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
							where
								maj_del = 0
							group by
								maj_ins_id
						) as t5 on ins_id = t5.t5_ins_id
					where
						ins_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					group by
						ins_id
					order by
						".(($order == "rand()")?($order):(Common::underscore($order)." ".$dir))."
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
						";
		$result = $this->db->query($qs);
		//Common::pr($this->db->last_query());

		$getInstitute = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {

				$thumb = preg_split("/\./", $row->e2i_image_name);
				$len = strlen($thumb[count($thumb)-1]);

				$getInstitute[] = array(
					"insId" => $row->ins_id,
					"insName" => $row->ins_name,
					"insUrlName" => $row->ins_url_name,
					"insCityId" => $row->ins_city_id,
					"insPriority" => $row->ins_priority,
					"cityName" => $row->city_name,
					"couName" => $row->cou_name,
					"conName" => $row->con_name,
					"cityUrlName" => $row->city_url_name,
					"couUrlName" => $row->cou_url_name,
					"conUrlName" => $row->con_url_name,
					"insLatitude" => $row->ins_latitude,
					"insLongitude" => $row->ins_longitude,
					"insImageNameThumb" => (($row->e2i_image_name)?(substr($row->e2i_image_name, 0, -($len+1))."_thumb.".$thumb[count($thumb)-1]):("")),
					"insImageAlt" => (($row->e2i_alt)?($row->e2i_alt):("")),
					"insInsId" => $row->ins_ins_id,
					"parentInsName" => $row->parent_ins_name,
					"childInsName" => $row->child_ins_name,
					"e2cTempFlag" => $row->e2c_temp_flag,
					"insPresenter" => $row->ins_presenter,
					"majCnt" => $row->maj_cnt,
					"majActiveCnt" => $row->maj_active_cnt,
					"insCnt" => $row->ins_cnt,
					"insActiveCnt" => $row->ins_active_cnt,
				);
				if($getAddresses) {
					$arrInsId[] = $row->ins_id;
				}
			}
		}

		if($getAddresses and isset($arrInsId) and count($arrInsId)) {
			$qs =	"
						select
							e2c_ent_id, e2c_text
						from
							".$this->db->dbprefix."ent2cat
							join ".$this->db->dbprefix."category on cat_id = e2c_cat_id and cat_name = 'Address' and cat_del = 0
						where
							e2c_entity in ('INS', 'FAC') and e2c_del = 0 and e2c_ent_id in (".implode(",", $arrInsId).")
						";
			$result = $this->db->query($qs);
			if($result->num_rows()) {
				foreach ($result->result() as $row) {
					foreach($getInstitute as $ii => $insData) {
						if($insData["insId"] == $row->e2c_ent_id) {
							$getInstitute[$ii]["insAddress"] = $row->e2c_text;
						}
					}
				}
			}
		}

		return $getInstitute;
	}

	function getInstituteCount($where = NULL, $fields, $query) {

		$fieldsQuery = Common::fieldsquery($fields, $query);

		$qs =	"
					select
						count(*) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0

						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin() and !defined("OVERRIDE"))?
						(" join ".$this->db->dbprefix."usr2ent on u2e_del = 0 and u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_ins_id = ins_id) and u2e_page_cat = '".((defined("ENTITY"))?(ENTITY):("IMP"))."' "):(""))."

					where
						ins_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					";
		$result = $this->db->query($qs);

		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				return $row->cnt;
			}
		}
	}

	function getParents($insInsId, $where = NULL) {
		$getParents = array();
		while($insInsId > 0) {
			$qs =	"
					select
						ins_id, ins_ins_id, ins_name, ins_url_name
					from
						".$this->db->dbprefix.$this->tableName."
						left join ".$this->db->dbprefix."ent2cat on e2c_entity in ('INS', 'FAC') and ins_id = e2c_ent_id and e2c_del = 0
					where
						ins_id = '".intval($insInsId)."' and ins_del = 0 ".((is_null($where))?(""):(" and ".$where))."
					group by
						ins_id
					";
			$result = $this->db->query($qs);
			$insInsId = 0;

			if($result->num_rows()) {
				foreach ($result->result() as $row) {
					$getParents[$row->ins_id]["insName"] = $row->ins_name;
					$getParents[$row->ins_id]["insUrlName"] = $row->ins_url_name;
					if($row->ins_ins_id) {
						$insInsId = $row->ins_ins_id;
					}
				}
			}
		}
		return array_reverse($getParents, TRUE);
	}

	function getChildren($insIdList, $where = NULL) {
		$getChildren = array();
		while($insIdList) {
			$qs =	"
					select
						ins_id, ins_ins_id, ins_name, ins_url_name
					from
						".$this->db->dbprefix.$this->tableName."
						left join ".$this->db->dbprefix."ent2cat on e2c_entity in ('INS', 'FAC') and ins_id = e2c_ent_id and e2c_del = 0
					where
						ins_ins_id in (".$insIdList.") and ins_del = 0 ".((is_null($where))?(""):(" and ".$where))."
					group by
						ins_id
					";
			$result = $this->db->query($qs);
			$insIdList = "";

			if($result->num_rows()) {
				foreach ($result->result() as $row) {
					$getChildren[] = array (
						"insId" => $row->ins_id,
						"insInsId" => $row->ins_ins_id,
						"insName" => $row->ins_name,
						"insUrlName" => $row->ins_url_name,
					);
					$insIdList .= (($insIdList)?(","):("")).$row->ins_id;
				}
			}
		}

		$new = array();
		foreach ($getChildren as $child) {
			$new[$child["insInsId"]][] = $child;
		}
		$tree = array();
		if(count($new)) {
			$tree = $this->_createTree($new, current($new));
		}

		return $tree;
	}

	function _createTree(&$list, $parent) {
		$tree = array();
		foreach ($parent as $k => $l){
			if(isset($list[$l["insId"]])){
				$l["children"] = $this->_createTree($list, $list[$l["insId"]]);
			}
			$tree[] = $l;
		} 
		return $tree;
	}
	
	function getAddress($insId) {
		$this->load->model("ent2cat");
		$getEnt2cat = $this->ent2cat->getEnt2cat($where = "e2c_entity in ('INS', 'FAC') and e2c_ent_id = '".$this->db->escape_str($insId)."'");
		if(isset($getEnt2cat[$this->config->item("categoryINSAddress")])) {
			return $getEnt2cat[$this->config->item("categoryINSAddress")]["e2cText"].", ".$getInstitute[0]["cityName"].((isset($getEnt2cat[$this->config->item("categoryINSPostalCode")]))?(", ".$getEnt2cat[$this->config->item("categoryINSPostalCode")]["e2cInt"]):(""));
		}
		return "";
	}

	function fixUrlNames() {
		if(!defined("AUTH_OVERRIDE")) define("AUTH_OVERRIDE", TRUE);
		$qs = "select ins_id, ins_ins_id, ins_name, ins_url_name from ".$this->db->dbprefix."institute where ins_del = 0";
		$result = $this->db->query($qs);
		$getInstitute = array();
		$arrFixed = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				if(!$row->ins_ins_id) {
					$arrFixed[$row->ins_id] = url_title($row->ins_name, "underscore", TRUE);
				} else {
					$getInstitute[$row->ins_id]["ins_ins_id"] = $row->ins_ins_id;
					$getInstitute[$row->ins_id]["ins_url_name"] = url_title($row->ins_name, "underscore", TRUE);
				}
			}
			while(!empty($getInstitute)) {
				foreach($getInstitute as $insId => $insData) {
					if(isset($arrFixed[$insData["ins_ins_id"]])) {
						$ins_url_name = $arrFixed[$insData["ins_ins_id"]]."/".$insData["ins_url_name"];
						$this->institute->update($insId, array("ins_url_name" => $ins_url_name));
						$arrFixed[$insId] = $ins_url_name;
						unset($getInstitute[$insId]);
					}
				}
			}
		}
	}
	
	function getWholeTreeIds($insId) {
		$getWholeTreeIds = array();
		$qs =	"
				select
					ins_url_name
				from
					".$this->db->dbprefix.$this->tableName."
				where
					ins_id = '".intval($insId)."' and ins_del = 0
				";
		$result = $this->db->query($qs);

		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$parent = explode("/", $row->ins_url_name);
				$parent = $parent[0];
				$qs =	"
						select
							ins_id
						from
							".$this->db->dbprefix.$this->tableName."
						where
							(substr(ins_url_name, 1, ".(strlen($parent)+1).") = '".$parent."/"."' or ins_id = '".intval($insId)."') and ins_del = 0
						";
				$result = $this->db->query($qs);

				if($result->num_rows()) {
					foreach ($result->result() as $row) {
						$getWholeTreeIds[] = $row->ins_id;
					}
				}
			}
		}
		
		return $getWholeTreeIds;
	}
	
}